package persistence.Demo;

import persistence.common.ConnectionProperties;
import persistence.sqlserver.SqlServerConnection;
import utils.*;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class GenesysReport {

    public static void main(String[] args) throws Exception {
        new GenesysReport().run();
    }

    public void run() throws Exception{
        ConnectionProperties hfProperties = new ConnectionProperties();
        hfProperties.setServer("172.16.101.2");
        hfProperties.setPort(1433);
        hfProperties.setUsername("wd_hf");
        hfProperties.setPassword("Wd@1234");
        hfProperties.setDatabase("gene_dm");

        ConnectionProperties shProperties = new ConnectionProperties();
        shProperties.setServer("172.16.102.2");
        shProperties.setPort(1433);
        shProperties.setUsername("wd");
        shProperties.setPassword("Wd@1234");
        shProperties.setDatabase("gene_dm");

        List<CallReport> callReportList = new ArrayList<>();
        String fileDir = "/Users/lining/Desktop/GenesysReport";
        String fileName = DateUtils.formatDate("通话日报-yyyyMMdd",DateUtils.getBeforeTime(1))+".xls";
        String sql = "SELECT * FROM ( SELECT CASE  WHEN a.OBJECT_NAME BETWEEN 80001 AND 80999 THEN '合肥' WHEN a.OBJECT_NAME BETWEEN 86001 AND 86100 OR a.OBJECT_NAME BETWEEN 81001 AND 81100 THEN '上海' WHEN a.OBJECT_NAME BETWEEN 87001 AND 87100 OR a.OBJECT_NAME BETWEEN 83001 AND 83100 THEN '萍乡' WHEN a.OBJECT_NAME BETWEEN 88001 AND 88300 OR a.OBJECT_NAME BETWEEN 82001 AND 82300 THEN '成都' ELSE '' END AS 'area', a.OBJECT_NAME AS 'objectName', a.OBJECT_ID AS 'objectID', b.TIME_KEY AS 'timeKey', b.T_DIALING AS 'dialingTime' , b.N_DIALING AS 'dialingNum', b.T_OUTBOUND AS 'outboundTime', b.N_OUTBOUND AS 'outboundNum' , (b.T_LOGIN - b.T_OUTBOUND - b.T_DIALING) / (b.N_OUTBOUND + b.N_DIALING) AS 'averageInterval' FROM O_AGENT_DAY a INNER JOIN R_AGENT_DAY b ON a.OBJECT_ID = b.OBJECT_ID WHERE b.N_OUTBOUND <> 0 AND b.TIME_KEY = CONVERT(varchar(100), dateadd(day, -1, getdate()), 112) ) x WHERE x.area <> ''";
        System.out.println("文件名称："+fileName);
        callReportList.addAll(getDataList(hfProperties,sql));
        System.out.println("合肥数据获取成功");
        callReportList.addAll(getDataList(shProperties,sql));
        System.out.println("上海数据获取成功");
        XlsUtils.exportXLS(callReportList,fileDir,fileName);
        System.out.println("文件生成成功");
        String filePath = new StringBuffer(fileDir).append(File.separator).append(fileName).toString();
        SendMailUtil.sendReport(filePath);
        System.out.println("邮件发送成功");
    }

    public List<CallReport> getDataList(ConnectionProperties properties,String sql) throws Exception{
        Connection connection = null;
        List<CallReport> callReportList = null;
        try {
            callReportList = new ArrayList<CallReport>();
            connection = SqlServerConnection.getConnection(properties);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            ResultSetMetaData metaData =  resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()){
                CallReport callReport = new CallReport();
                for(int i = 1 ;i <= columnCount ; i++){
                    Reflections.setFieldValue(callReport, metaData.getColumnName(i),resultSet.getObject(i));
                }
                callReportList.add(callReport);
            }
            return callReportList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if(connection != null)connection.close();
        }
    }
}
